using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.IO;
using System.Diagnostics;


namespace CMS_SUN_IF_Extraction
{
    public partial class frmExport_2SUN_Form : Form
    {
        private BAL bao = new BAL();
        private Style styleColumnHeadings;
        private Style styleRows;
        private Workbook workbook;
        private Microsoft.Office.Interop.Excel.Application EXL = new Microsoft.Office.Interop.Excel.ApplicationClass();
        private Worksheet worksheet;        
        private string filePath = "";
        private System.Data.DataTable tblSysCtrl = null;
        public frmExport_2SUN_Form()
        {
            InitializeComponent();
        }

        private void Export_2SUN_Form_Load(object sender, EventArgs e)
        {
            //txtLastExtractdate.Text = DateTime.Parse(GlobalClass.gLast_Extract_Date).ToString("dd-MMM-yyyy");
            string curr_vat_center = "";
            string last_extractDate = "";
            tblSysCtrl = bao.get_tbl_SysControl();
            if (tblSysCtrl != null && tblSysCtrl.Rows.Count > 0)
            {
                DataRow r = tblSysCtrl.Rows[0];
                curr_vat_center = r["Curr_VAT_Center"].ToString();
                last_extractDate = DateTime.Parse(r["Last_Extract_date"].ToString()).ToString("dd-MMM-yyyy");
            }

            DateTime curday = DateTime.Now;
            string day = "00" + curday.Day.ToString();
            string month = "00" + curday.Month.ToString();
            string dirExtr = "C:\\temp";
            if (Directory.Exists(dirExtr) == false)
                Directory.CreateDirectory(dirExtr);
            txtOutput_ExcelFile.Text = dirExtr + "\\SUN-" + curr_vat_center + curday.Year.ToString() + month.Substring(month.Length-2) + day.Substring(day.Length-2) + ".xls";
            filePath = txtOutput_ExcelFile.Text.Trim();
            //txtLastExtractdate.Text = bao.get_Last_Extract_Date();
            txtLastExtractdate.Text = last_extractDate;
            pb.Visible = false;

        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            /*--Thien rem on 14-Dec-2009
            int uGet_XOS_COunt;
             --End Thien*/
            //int position = 0;
            DialogResult uConfirmResponse;
            string uErrorMsg;

            if (txtOutput_ExcelFile.Text.Trim() == "")
            {
                MessageBox.Show("Please specify the Output Excel file location !!");
                txtOutput_ExcelFile.Focus();
                txtOutput_ExcelFile.SelectAll();
                return;
            }

            txtStatus_bar.Text = "Verifying records before export ...";
            bao.gVerify_b4_Export();
            if (GlobalClass.grecords_Verify_ok == false)
            {
                MessageBox.Show("There are some invalid records. \n Records not exported..","",MessageBoxButtons.OK,MessageBoxIcon.Exclamation);
                //added on 07-Sep-2009
                //return;
                //end
            }
            else
            {
                uErrorMsg = "";

                if (GlobalClass.gMissing_VAT==true)
                    uErrorMsg = "There are some missing VAT # in the extraction range !!";
                /*--Thien rem on 14-Dec-2009
                //' to prompt for X Outstanding Payment Mode Alert
                uGet_XOS_COunt = bao.gCheck_XOS_Payment_Count();

                if (uGet_XOS_COunt > 0 || GlobalClass.gMissing_VAT == true)
                    uErrorMsg = uErrorMsg + " \n There are still " + uGet_XOS_COunt + " records with (X) Outstanding Payment Mode in the Extracted file !!!";
                --End Thien*/

                if (uErrorMsg != "")
                {
                    uErrorMsg = uErrorMsg + "\nAre you sure you want to proceed ?";
                    uConfirmResponse = MessageBox.Show(uErrorMsg, "Verification check", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation);

                    if (uConfirmResponse != DialogResult.Yes)  //' Select other the YES
                        return;
                }             
            }
            pb.Visible = true;
            txtStatus_bar.Text = "Exporting records into Excel file ...";
            System.Data.DataTable tblOutput = bao.get_lst_Extract_Final_Output();

            //export to Excel file
            
            if (File.Exists(filePath))
            {
                /*workbook = EXL.Workbooks.Open(filePath, 0, false, 5, "", "", false,
                                             Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
                                             "", true, false, 0, true, false, false);*/
                //modified
                //worksheet = (Worksheet)workbook.Sheets[1];
                //Microsoft.Office.Interop.Excel.Range myAllRange = worksheet.Cells;
                //myAllRange.Select();
                //myAllRange.CurrentRegion.Select();
                //myAllRange.ClearContents();

                //end
                try
                {
                    File.Delete(filePath);
                }
                catch(IOException ioe)
                {
                    MessageBox.Show(ioe.Message, "warning", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    return;
                }
            }
           
            workbook = EXL.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            worksheet = (Worksheet)workbook.Sheets.get_Item(1);
            worksheet.Name = "qExtract_Final_Output";

            //worksheet.get_Range(worksheet.Cells[1, 6], worksheet.Cells[65535, 6]).EntireColumn.NumberFormat = "";
            //added on 3/8/2009
                SetUpStyles();
            //end
        
            int row, col;
            pb.Maximum = tblOutput.Rows.Count + 1;//* 100 + 1;
            row = 1;
            col = 1;
            for (int i = 0; i < tblOutput.Columns.Count; i++)
            {
                fillExcelCell(worksheet, row, col++, tblOutput.Columns[i].ColumnName,styleColumnHeadings.Name);
            }

            //Add DataView Rows To Worksheet
            row = 2;
            col = 1;
            string value = "";
            for (int i = 0; i < tblOutput.Rows.Count; i++)
            {

                for (int j = 0; j < tblOutput.Columns.Count; j++)
                {
                    if (tblOutput.Columns[j].ColumnName == "Invoice Dt" || tblOutput.Columns[j].ColumnName == "Payment Dt")
                        value = String.Format("{0:dd-MMM-yyyy}", DateTime.Parse(tblOutput.Rows[i][j].ToString()));
                    else if (tblOutput.Columns[j].ColumnName == "T3")
                        value = "6OT";
                    else if (tblOutput.Columns[j].ColumnName == "Patient_Name" && (tblOutput.Rows[i][j].ToString().Length >= 25))
                    {
                        value = tblOutput.Rows[i][j].ToString().Substring(0, 25);
                    }
                    else if (tblOutput.Columns[j].ColumnName == "T2")
                    {
                        value = "'" + tblOutput.Rows[i][j].ToString() ;
                    }
                    else
                        value = tblOutput.Rows[i][j].ToString();

                    fillExcelCell(worksheet, row, col++, value,styleRows.Name);
                }
                col = 1;
                row++;

                //position = (100 / tblOutput.Rows.Count) * row + 2;
                pb.Value += 1;//position;

            }
            try
            {
                workbook.Close(true, filePath, Type.Missing);
                EXL.UserControl = false;
                EXL.Quit();
                EXL = null;
                killExcel();
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            pb.Value = 0;

            pb.Visible = false;
            txtStatus_bar.Text = "Updating EXTRACTED indicator...";
            bao.Upd_ImportSUN_IND_newRevenue(DateTime.Now.ToString("dd-MMM-yyyy"));
            
            //update last_extract_date
            bao.update_last_extract(GlobalClass.gCurr_VAT_Center,
                                    GlobalClass.gCurr_Start_ExtractDate,
                                    GlobalClass.gCurr_End_ExtractDate,
                                    //GlobalClass.gLast_Extract_Date);
                                    DateTime.Now.ToString("dd-MMM-yyyy"));
            txtLastExtractdate.Text = DateTime.Now.ToString("dd-MMM-yyyy");
            MessageBox.Show("Export completed !!");
        }

        private void fillExcelCell(Worksheet worksheet, int row, int col, string value, string StyleName)
        {
            Range rng = (Range)worksheet.Cells[row, col];
            
            //rng.Select();                 
            rng.Value2 = value;
            if (StyleName == "styleColumnHeadings")
                rng.Style = StyleName;            
            rng.Columns.EntireColumn.AutoFit();
           
            rng.Borders.Weight = XlBorderWeight.xlThin;
            rng.Borders.LineStyle = XlLineStyle.xlContinuous;
            rng.Borders.ColorIndex = XlColorIndex.xlColorIndexAutomatic;
            
        }
        private void SetUpStyles()
        {
            try
            {
                styleColumnHeadings = workbook.Styles["styleColumnHeadings"];

            }
            catch
            {
                styleColumnHeadings =
                   workbook.Styles.Add("styleColumnHeadings", Type.Missing);
                styleColumnHeadings.Font.Name = "Arial";
                styleColumnHeadings.Font.Bold = true;
                styleColumnHeadings.Font.Size = 12;
                styleColumnHeadings.Font.Color =
                  (255 << 16) | (255 << 8) | 255;
                styleColumnHeadings.Interior.Color = 
                  (0 << 16) | (0 << 8) | 220;
               /* styleColumnHeadings.Interior.Pattern =
                   Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid;*/

            }

            // Style styleRows
            try
            {
                styleRows = workbook.Styles["styleRows"];
            }
            // Style doesn't exist yet.
            catch
            {
                styleRows = workbook.Styles.Add("styleRows", Type.Missing);
                styleRows.Font.Name = "Arial";
                styleRows.Font.Size = 10;
                styleRows.Font.Color = (0 << 16) | (0 << 8) | 0;
                styleRows.Interior.Color = //Color.White;
                    (255 << 16) | (255 << 8) | 255;
                /*styleRows.Interior.Pattern =
                     Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid;*/
            }
        }
        private void killExcel()
        {
            try
            {
                Process[] ps = Process.GetProcesses();
                foreach (Process p in ps)
                {
                    if (p.ProcessName.ToLower().Equals("excel"))
                    {
                        p.Kill();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("ERROR " + ex.Message);
            }
        }

    }
}